前端复杂 table 渲染及 excel.js 导出

/**
 * 表格单元格配置
 */
export interface TableCell {
  /** 展示文案 */
  text?: string;
  /** 文字尺寸，默认 14 */
  fontSize?: number;
  /** 文字是否加粗 */
  bold?: boolean;
  /** 文字颜色，默认 #000000 */
  color?: string;
  /** 水平对齐方式，默认 center */
  align?: "left" | "center" | "right";
  /** 所占行数，默认 1 */
  rowspan?: number;
  /** 所占列数，默认 1 */
  colspan?: number;
  /** 高度，若一行中有多个单元格设置高度，将使用其中的最大值 */
  height?: number;
  /** 背景颜色 */
  bgColor?: string;
  /** 是否绘制对角线 */
  diagonal?: boolean;
  /** 是否绘制边框，默认 true */
  border?: ("top" | "right" | "bottom" | "left")[];
  /** 动态属性 */
  [key: string]: any;
}

/**
 * 表格行。undefined 标识被合并的单元格
 */
export type TableRow = (TableCell | undefined)[];

import ExcelJS, { Workbook, Worksheet } from "exceljs";

/**
 * 生成 excel 文件
 */
export async function generateExcel(
  rowList: TableRow[],
  colWidth: number | number[] = []
): Promise<ExcelJS.Workbook> {
  // 创建表
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet("Sheet1");
  // 插入表头和数据
  rowList.forEach((row) =>
    worksheet.addRow(row.map((cell) => cell?.text || ""))
  );
  // 合并单元格
  rowList.forEach((rowItem, rowIndex) => {
    rowItem.forEach((cellItem, colIndex) => {
      if (!cellItem) {
        return;
      }
      const colNoStart = convertColumnNo(colIndex);
      const colNoEnd = convertColumnNo(colIndex + (cellItem.colspan || 1) - 1);
      const rowNoStart = rowIndex + 1;
      const rowNoEnd = rowNoStart + (cellItem.rowspan || 1) - 1;
      worksheet.mergeCells(`${colNoStart}${rowNoStart}:${colNoEnd}${rowNoEnd}`);
    });
  });
  // 设置列宽
  let colWidthList: number[];
  if (Array.isArray(colWidth)) {
    colWidthList = colWidth;
  } else {
    colWidthList = new Array(rowList[0].length).fill(colWidth);
  }
  colWidthList.forEach((width, index) => {
    worksheet.getColumn(index + 1).width = width / 7.8;
  });
  // 设置默认行高
  worksheet.properties.defaultRowHeight = 28;
  // 设置单元格样式
  rowList.forEach((rowItem, rowIndex) => {
    const row = worksheet.getRow(rowIndex + 1);
    let maxHeight = worksheet.properties.defaultRowHeight;
    rowItem.forEach((cellItem, colIndex) => {
      if (!cellItem) {
        return;
      }
      const cell = row.getCell(colIndex + 1);
      maxHeight = Math.max(maxHeight, cellItem.height || 0);
      // 文字样式
      cell.font = {
        name: "等线",
        size: ((cellItem.fontSize || 14) * 11) / 14, // Excel 字体大小为 11
        bold: cellItem.bold,
        color: { argb: (cellItem.color || "#000000").slice(1) },
      };
      const border = cellItem?.border || ["top", "right", "bottom", "left"];
      // 设置边框
      cell.border = {
        top: border.includes("top") ? { style: "thin" } : undefined,
        right: border.includes("right") ? { style: "thin" } : undefined,
        bottom: border.includes("bottom") ? { style: "thin" } : undefined,
        left: border.includes("left") ? { style: "thin" } : undefined,
        diagonal: { up: false, down: cellItem?.diagonal, style: "thin" },
      };
      // 设置居中&自动换行
      cell.alignment = {
        horizontal: cellItem.align || "center",
        vertical: "middle",
        wrapText: true,
      };
      // 设置背景
      if (cellItem.bgColor) {
        cell.fill = {
          type: "pattern",
          pattern: "solid",
          fgColor: { argb: cellItem.bgColor.slice(1) },
        };
      }
    });
    row.height = maxHeight;
  });
  return workbook;
}

/**
 * 转换数字列号为字母列号
 * @param num
 */
function convertColumnNo(num: number) {
  const codeA = "A".charCodeAt(0);
  const codeZ = "Z".charCodeAt(0);
  const length = codeZ - codeA + 1;
  let result = "";
  while (num >= 0) {
    result = String.fromCharCode((num % length) + codeA) + result;
    num = Math.floor(num / length) - 1;
  }
  return result;
}

/**
 * 下载为 excel 文件
 * @param workbook excel 工作簿对象
 * @param fileName 文件名
 */
export async function downloadExcel(workbook: ExcelJS.Workbook, fileName: string) {
  const buffer = await workbook.xlsx.writeBuffer();
  const blob = new Blob([buffer], { type: "arraybuffer" });
  const link = document.createElement("a");
  link.href = URL.createObjectURL(blob);
  link.download = fileName;
  link.click();
}

/**
 * 渲染图片等非普通文本的数据
 */
export type RenderAdditionalData = (
  /** 行号 */
  rowIndex: number,
  /** 列号 */
  colIndex: number,
  /** excel 工作簿对象 */
  workbook: ExcelJS.Workbook,
  /** excel 工作表对象 */
  worksheet: ExcelJS.Worksheet
) => Promise<void> | void;

async function generateExcel(
  rowList: TableRow[],
  colWidth: number | number[] = [],
  renderAdditionalData?: RenderAdditionalData
): Promise<ExcelJS.Workbook> {
  ...
  // 合并单元格
  rowList.forEach((rowItem, rowIndex) => {
    ...
  });

  // 渲染图片等非普通文本的数据
  if(renderAdditionalData) {
    for (let rowIndex = 0; rowIndex < rowList.length; rowIndex++) {
      const rowItem = rowList[rowIndex];
      for (let colIndex = 0; colIndex < rowItem.length; colIndex++) {
        if (!rowItem[colIndex]) {
          continue;
        }
        await renderAdditionalData(rowIndex, colIndex, workbook, worksheet);
      }
    }
  }

  // 设置默认行高
  worksheet.properties.defaultRowHeight = 28;
  ...
}